﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using MySql.Data.MySqlClient;

namespace HRSystem.Utility
{
    public abstract class MySqlUtil
    {
        private static readonly string defaultConn = System.Configuration.ConfigurationManager.ConnectionStrings["HRSystem.Default.ConnectionString"].ConnectionString;
       
        private static string BuildConnStr(string databaseName)
        {
            string connStr = defaultConn;
            return connStr;
        }

        private static void PrepareCommand(MySqlCommand cmd,MySqlConnection conn,string cmdText,MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        public static int ExecuteNonQuery(string cmdText,MySqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(cmdText,cmdParms,String.Empty);
        }

        public static int ExecuteNonQuery(string cmdText,MySqlParameter[] cmdParms,string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
                return val;
            }
        }

        public static bool ExecuteNonQuery(CommandType commandType,string[] commandText,params MySqlParameter[][] commandParameters)
        {
            return ExecuteNonQuery(String.Empty,commandType,commandText,commandParameters);
        }

        public static bool ExecuteNonQuery(string databaseName,CommandType commandType,string[] commandText,params MySqlParameter[][] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            bool bSucess = true;

            using (MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName)))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;

                using (MySqlTransaction transaction = conn.BeginTransaction())
                {
                    int result;
                    for (int i = 0; i < commandText.Length; i++)
                    {
                        result = 0;
                        try
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = commandText[i];
                            if (commandParameters[i] != null)
                            {
                                foreach (MySqlParameter parm in commandParameters[i])
                                    cmd.Parameters.Add(parm);
                            }

                            result = cmd.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            bSucess = false;
                            break;
                        }

                        if (result <= 0)
                        {
                            transaction.Rollback();
                            bSucess = false;
                            break;
                        }
                    }

                    if (bSucess)
                    {
                        transaction.Commit();
                    }
                }

            }

            return bSucess;
        }

        public static object ExecuteScalar(string cmdText,MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(defaultConn))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        public static object ExecuteScalar(string cmdText,MySqlParameter[] cmdParms,string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        public static DataSet GetDataSet(string cmdText,MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(defaultConn))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

        public static DataSet GetDataSet(string cmdText,MySqlParameter[] cmdParms,string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

        public static DataTable GetDataTable(string cmdText,MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(defaultConn))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds.Tables[0];
            }
        }

        public static DataTable GetDataTable(string cmdText,MySqlParameter[] cmdParms,string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds.Tables[0];
            }
        }

        public static MySqlDataReader GetDataReader(string cmdText,MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(defaultConn);
            try
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static MySqlDataReader GetDataReader(string cmdText,MySqlParameter[] cmdParms,string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName));
            try
            {
                PrepareCommand(cmd,conn,cmdText,cmdParms);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static bool IsConnect(string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            bool bConnected = true;
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr(databaseName)))
            {
                try
                {
                    conn.Open();
                    conn.Close();
                }
                catch
                {
                    bConnected = false;
                }
            }
            return bConnected;
        }
    }
}
